<?php

/**
 * Created by PhpStorm.
 * User: qiliping
 * Date: 2016/12/7
 * Time: 上午10:52
 */
class User_model extends CI_Model
{
  /**
   * 获取学校会员列表
   * @param  [type] $limit     [description]
   * @param  [type] $offset    [description]
   * @param  [type] $school_id [description]
   * @param  [type] $name      [description]
   * @param  [type] $phone     [description]
   * @return [type]            [description]
   */
  function get_users_list($limit=0, $offset=10, $conditions=array()){
    $this->db->trans_start();
    $where = "is_teacher = 0";
    if(!empty($conditions['school_id'])){
      $where .= " AND school_id = '{$conditions['school_id']}'";
    }
    if(!empty($conditions['mobile'])){
      $where .= " AND mobile = '{$conditions['mobile']}'";
    }
    if(!empty($conditions['nick'])){
      $where .= " AND nick like '%{$conditions['nick']}%'";
    }
    $sql = "SELECT SQL_CALC_FOUND_ROWS u.id,u.nick,u.mobile,u.is_vip,u.vip_start_day,u.vip_expire_day,u.add_time,ua.balance,ua.commission_total,
            (select mobile from {$this->db->dbprefix}user where id= u.parent1) as parent1_mobile,
            (select count(0) from {$this->db->dbprefix}user where parent1= u.id) as parent1_count,
            (select count(0) from {$this->db->dbprefix}user where parent2= u.id) as parent2_count,
            (select count(0) from {$this->db->dbprefix}user where parent3= u.id) as parent3_count
            FROM {$this->db->dbprefix}user u
            left join {$this->db->dbprefix}user_account ua on ua.user_id = u.id
             WHERE $where order by id desc limit $limit,$offset ";

    $result = $this->db->query($sql)->result_array();
    $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();

    $this->db->trans_complete();

    return array("total_count" => $row_num['row_num'], "result" => $result);
  }

  /**
   * 设置VIP
   * @param [type] $user_id [description]
   * @param [type] $end_day [description]
   */
  function set_user_vip($user_id,$end_day){
    $user_info = $this->get_user_by_id($user_id);
    if($user_info){
      if($user_info['is_vip'] == 0){
        //减少机构可用帐号数量
        $time = time();
        $today_time = strtotime(date('Y-m-d'));
        $sql = "UPDATE {$this->db->dbprefix}o2o_school_video_user_codes SET status = 2, use_uid='{$user_id}', use_time='{$time}' WHERE school_id = '{$this->user_data['school_id']}' AND status = 1 AND expire_time >= '{$today_time}' ORDER BY id ASC LIMIT 1";
        $this->db->query($sql);

        $this->db->where("id", $this->user_data['school_id']);
        $this->db->set("item_num", "item_num - 1", false);
        $this->db->update("o2o_school");
      }
      $user = array('is_vip' => 1,'vip_expire_day'=> strtotime($end_day) );

      if($user_info['vip_start_day']==0){
        $user['vip_start_day'] = time();
      }

      return $this->db->update('user',$user, array('id' => $user_id ));
    }
    return 0;

  }
  /**
  * 注册会员
  **/
  /**public function reg_user($phone,$rand_str,$password,$register_ip)
  {
      //事务开始
		  $this->db->trans_start();

      $user_data = array(
      'login_name' => '',
      'rand_str' => $rand_str,
      'password' => $password,
      'mobile' => $phone,
      'is_reg_xmpp' => 0,
      'sex' => 0,
      'register_ip' => $register_ip,
      'status' => 1,
      'add_time' => time(),
      'edit_time' => 0,
    );
    $this->db->insert('user', $user_data);
    $user_id = $this->db->insert_id();

    //user_account
    $user_account = array(
      'user_id' => $user_id,
      'balance' => 0,
      'integral' => 0,
      'commission_total' => 0
    );
    $this->db->insert('user_account', $user_account);

    $this->db->trans_complete();
	  return $user_id;
  }
  */

  /**
  * 根据用户id获取user_account
  */
  public function get_user_account_by_id($user_id)
  {
    $sql = "SELECT * FROM {$this->db->dbprefix}user_account where user_id = '{$user_id}' LIMIT 1";
    return $this->db->query($sql)->row_array();
  }

  /**
  * 根据手机号获取用户信息
  */
  function get_user_by_phone($phone){
    $sql = "SELECT * FROM {$this->db->dbprefix}user where mobile = '{$phone}' LIMIT 1";
    return $this->db->query($sql)->row_array();
  }

  /**
   * 根据用户名或手机号获取用户
   * @param  [type] $user_name [description]
   * @return [type]            [description]
   */
  public function get_user_by_phone_or_username($user_name)
  {
    $sql = "SELECT * FROM {$this->db->dbprefix}user where mobile = '{$user_name}' OR login_name='{$user_name}' LIMIT 1";
    return $this->db->query($sql)->row_array();
  }

  /**
  * 根据主键id获取用户信息
  */
  function get_user_by_id($user_id){
    $sql = "SELECT * FROM {$this->db->dbprefix}user where id = '{$user_id}' LIMIT 1";
    return $this->db->query($sql)->row_array();
  }


  function check_school_manage($teacher_id){
    $sql = "SELECT is_manager from {$this->db->dbprefix}o2o_teachers where id = ".$teacher_id."";
    $result = $this->db->query($sql)->row_array();
    return $result['is_manager'];
  }

  function get_user_item_terms($conditions, $limit = 0, $offset = 10){
    $where = "sit.school_id = '{$this->user_data['school_id']}'";
    if(!empty($conditions['uid'])){
      $where .= " AND sit.uid='{$conditions['uid']}' ";
    }

    if(!empty($conditions['lost_status'])){
      $time = time();
      if($conditions['lost_status'] == 1){//未过期
        $where .= " AND sit.lose_time > $time ";
      }else{//已过期
        $where .= " AND sit.lose_time < $time ";
      }
    }

    if(!empty($conditions['data_type'])){
      if($conditions['data_type'] > 0 && $conditions['data_type'] < 4){
        $where .= " AND sit.data_type=1 AND c.type='{$conditions['data_type']}'";  
      }else{
        $where .= " AND sit.data_type='{$conditions['data_type']}'";  
      }
    }
    $sql = "SELECT SQL_CALC_FOUND_ROWS sit.*, c.title, c.type, s.title AS s_title FROM {$this->db->dbprefix}o2o_student_item_terms AS sit ";
    $sql .= "LEFT JOIN {$this->db->dbprefix}course AS c ON(c.id=sit.data_id) ";
    $sql .= "LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=sit.data_id) ";
    $sql .= "WHERE $where ORDER BY sit.id DESC LIMIT $limit, $offset ";
    $result = $this->db->query($sql)->result_array();
    $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
    return array("count" => $row_num['row_num'], "result" => $result);
  }

  function get_school_course($course_id = 0){
    $where = " WHERE cs.status = 1 AND cs.school_id = '{$this->user_data['school_id']}' ";
    if(!empty($course_id)){
      $where .= " AND c.id = '{$course_id}' ";
    }
    $sql = "SELECT c.id,c.type,c.title,c.school_id,c.effective_time,cs.fx_price,cs.is_free,cs.uid AS fx_uid FROM {$this->db->dbprefix}course_shelves AS cs ";
    $sql .= " LEFT JOIN {$this->db->dbprefix}course AS c ON (course_id = c.id) ";
    $sql .= " $where ORDER BY c.id DESC";
    $result = $this->db->query($sql)->result_array();
    return $result;
  }

  function get_school_tiku($subject_id = 0){
    $where = "WHERE status = 1 AND tp.id > 0 AND s.id > 0 AND tp.school_id = '{$this->user_data['school_id']}' ";
    if(!empty($subject_id)){
      $where .= " AND s.id = '{$subject_id}' ";
    }
    $sql = "SELECT SQL_CALC_FOUND_ROWS tp.*, s.title,s.img,s.school_id,s.id FROM edu_o2o_school_tk_packages AS tp LEFT JOIN edu_b_subject AS s ON(s.id=tp.subject_id) $where";
    $result = $this->db->query($sql)->result_array();
    return $result;
  }

}
